#! /bin/bash

# 主库 创建迁移所需用户
# create user dba_qianyi_m@'%' identified by '123456'; grant all on *.* to dba_qianyi_m@'%' with grant option; flush privileges;
# 从库 创建迁移所需用户
# create user dba_qianyi_s@'%' identified by '123456'; grant all on *.* to dba_qianyi_s@'%' with grant option; flush privileges;

# 主库连接信息
master_host='10.10.8.65'
master_port=3306
master_user='dba_qianyi_m'
master_pwd='123456'

# 从库连接信息
slave_host='10.10.8.66'
slave_port=3306
slave_user='dba_qianyi_s'
slave_pwd='123456'

# 复制账号
replic_host='10.10.8.66'
replic_user='repl_defds'
replic_pwd='123456'

# 基于 gtid 复制: ON/OFF
gtid_replic='ON'

# 检查 binlog 是否为 row 模式： 0 check /1 no check
binlog_format_check=1

# 复制线程数：1 ~ CPU*2
replic_threads_num=20

# 延迟复制: 秒/单位 28800=8小时
replic_delay_time=0

# mydumper 备份线程数
mydumper_threads=8

# myloader 恢复线程数
myloader_threads=8

# myduper 长查询保护时长/秒
mydumper_kill_long_queries_time=120

# mydumper/myloader 备份输出目录
mydumper_outputdir="/mydata/backup/3306/backup"  
myloader_outputdir="/mydata/backup/3306/restore"

# mydumper/myloader/mysql 命令路径 
mydumper_command="$(which  mydumper)"
myloader_command="$(which  myloader)"
mysql_command="$(which  mysql)"

# mydumper/myloader 日志路径
mydumper_log="/tmp/mydumper_$(date +%Y%m%d%H%M%S).log"
myloader_log="/tmp/myloader_$(date +%Y%m%d%H%M%S).log"
log_redirection="/tmp/replic_$(date "+%Y-%m-%d-%H%M%S").log"


# mysql 连接
mysql_master_conn="$mysql_command -h$master_host -P$master_port -u$master_user -p$master_pwd"
mysql_slave_conn="$mysql_command  -h$slave_host  -P$slave_port  -u$slave_user  -p$slave_pwd"

# 检查
check() {
  check_status=0

  # 测试 master 连通性
  ${mysql_master_conn}  -e 'select 1' >/dev/null 2>&1
  if [ $? -ne 0 ];then 
    echo "$(date "+%Y-%m-%d %H:%M:%S")  master 连接失败 请检查网络及用户密码"  >>${log_redirection}
    cat $log_redirection
    exit 1
  fi

  # 测试 slave 连通性
  ${mysql_slave_conn} -e 'select 1' >/dev/null 2>&1
  if [ $? -ne 0 ];then 
    echo "$(date "+%Y-%m-%d %H:%M:%S")  slave 连接失败 请检查网络及用户密码"  >>${log_redirection}
    cat $log_redirection
    exit 1
  fi
  which  mydumper  >/dev/null 2>&1
  if [ $? -ne 0 ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  mydumper 命令不存在"  >>${log_redirection}
    check_status=1
  fi

  which  myloader  >/dev/null 2>&1
  if [ $? -ne 0 ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  myloader 命令不存在"  >>${log_redirection}
    check_status=1
  fi

  # 检查目录是否存在
  if [ ! -d "$mydumper_outputdir" ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  $mydumper_outputdir 目录不存在"  >>${log_redirection}
    check_status=1
  fi

  if [ ! -d "$myloader_outputdir" ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  $myloader_outputdir 目录不存在"  >>${log_redirection}
    check_status=1
  fi

  # 检查目录是否为空
  mydumper_outputdir_file_count=$(ls -A ${mydumper_outputdir} 2>/dev/null |wc -l)
  myloader_outputdir_file_count=$(ls -A ${myloader_outputdir} 2>/dev/null |wc -l)
  if [ $mydumper_outputdir_file_count -ne 0 ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  $mydumper_outputdir 目录不为空"  >>${log_redirection}
    check_status=1
  fi

  if [ $myloader_outputdir_file_count -ne 0 ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  $myloader_outputdir 目录不为空"  >>${log_redirection}
    check_status=1
  fi  

  # 检查 server id  是否相同
  diff_server_id_master=$(${mysql_master_conn} -N  -e 'select @@server_id' 2>/dev/null)
  diff_server_id_slave=$(${mysql_slave_conn}   -N  -e 'select @@server_id' 2>/dev/null)

  if [[ $diff_server_id_master == $diff_server_id_slave ]];then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  server_id 相同 请修改"  >>${log_redirection}
    check_status=1
  fi

  # 检查 gtid 模式是否相同
  master_gtid="$($mysql_master_conn -N -e "select @@global.gtid_mode" 2>/dev/null)"
  slave_gtid="$($mysql_slave_conn  -N -e "select @@global.gtid_mode" 2>/dev/null)"
  
  if [[ $master_gtid != $slave_gtid ]];then
   echo "$(date "+%Y-%m-%d %H:%M:%S")  主从 gtid_mode 不同 请修改为相同参数"  >>${log_redirection}
   check_status=1
  fi


  # 检查从库是否存在复制信息
  slave_status=$(${mysql_slave_conn} -N  -e 'show slave status' 2>/dev/null)
  if [ -n "$slave_status" ]; then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  从库存在主从信息 请检查后清除信息再试"  >>${log_redirection}
    check_status=1
  fi

  # 检查从库是否存在非系统库
  other_database=$(${mysql_slave_conn} -N  -e "SELECT TABLE_SCHEMA FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql')")
  if [ -n "$other_database" ]; then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  从库存在非系统库 请检查后清除信息再试"  >>${log_redirection}
    check_status=1
  fi


  # 检查 gtid 是否开启 / 强一致是否开启 
  if [ $gtid_replic = 'ON' ];then
      # 检查 gtid 强一致
      master_enforce_gtid_consistency="$($mysql_master_conn -N -e "select @@global.enforce_gtid_consistency" 2>/dev/null)"
      slave_enforce_gtid_consistency="$($mysql_slave_conn -N -e "select @@global.enforce_gtid_consistency" 2>/dev/null)"

      if [[ "$master_enforce_gtid_consistency" != 'ON' || "$slave_enforce_gtid_consistency" != 'ON'  ]];then
       echo "$(date "+%Y-%m-%d %H:%M:%S")  主或从 enforce_gtid_consistency 强一致没有开启"  >>${log_redirection}
       check_status=1
      fi
  fi

  # 检查行模式
  if [  $binlog_format_check -eq 1 ];then
      master_binlog_format="$($mysql_master_conn -N -e "select @@global.binlog_format" 2>/dev/null)"
      slave_binlog_format="$($mysql_slave_conn -N -e "select @@global.binlog_format" 2>/dev/null)"

      if [[ "$master_binlog_format" != 'ROW' || "$slave_binlog_format" != 'ROW'  ]];then
       echo "$(date "+%Y-%m-%d %H:%M:%S")  主或从 binlog_format 不为行模式 "  >>${log_redirection}
       check_status=1
      fi

      master_binlog_row_image="$($mysql_master_conn -N -e "select @@global.binlog_row_image" 2>/dev/null)"
      slave_binlog_row_image="$($mysql_slave_conn -N -e "select @@global.binlog_row_image" 2>/dev/null)"

      if [[ "$master_binlog_row_image" != 'FULL' || "$slave_binlog_row_image" != 'FULL'  ]];then
       echo "$(date "+%Y-%m-%d %H:%M:%S")  主或从 binlog_row_image 不为 FULL"  >>${log_redirection}
       check_status=1
      fi
  fi

  if [ $check_status -ne 0 ];then
    cat $log_redirection
    exit 1
  fi
}
check

create_replic_user() {

  # 检查用户是否存在
  repl_user_exists=$(${mysql_master_conn} -N  -e "show grants for ${replic_user}@'${replic_host}'" 2>/dev/null|grep -c -w "'${replic_user}'@'${replic_host}'" 2>/dev/null)
  if [ $repl_user_exists -ne 1 ];then
      # 创建复制用户
      ${mysql_master_conn} -e "create user ${replic_user}@${replic_host} identified by '${replic_pwd}';" >/dev/null 2>&1
      if [ $? -ne 0 ]; then
        echo "$(date "+%Y-%m-%d %H:%M:%S")  创建复制用户失败 请检查权限" >>${log_redirection}
        exit 1
      fi
  else 
    echo "$(date "+%Y-%m-%d %H:%M:%S")  复制用户已存在" >>${log_redirection}
  fi

  ${mysql_master_conn} -e "grant replication slave on *.* to ${replic_user}@${replic_host};" >/dev/null 2>&1
  if [ $? -ne 0 ]; then
    echo "$(date "+%Y-%m-%d %H:%M:%S")  创建复制用户失败 请检查权限" >>${log_redirection}
    exit 1
  fi
  ${mysql_master_conn} -e "flush privileges;" >/dev/null 2>&1

}
create_replic_user



# 将主库数据导入从库
import_data() {
${mydumper_command}  \
--build-empty-files \
--trx-consistency-only \
--threads=${mydumper_threads} \
--less-locking \
--compress-protocol \
--verbose=3 \
--triggers \
--events \
--routines \
--long-query-guard=${mydumper_kill_long_queries_time} \
--kill-long-queries \
--logfile=${mydumper_log} \
--host=${master_host} \
--port=${master_port} \
--user=${master_user} \
--password="${master_pwd}" \
--stream \
--outputdir=${mydumper_outputdir} 2>>${log_redirection}| \
${myloader_command} \
--threads=${myloader_threads} \
--enable-binlog \
--verbose=3 \
--logfile=${myloader_log} \
--overwrite-tables \
--host=${slave_host} \
--port=${slave_port} \
--user=${slave_user} \
--password="${slave_pwd}" \
-d ${myloader_outputdir} \
--stream  2>>${log_redirection}

if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]]; then
  echo  "$(date +"%Y%m%d %H:%M:%S") 数据导入失败 请检查错误信息" >>${log_redirection}
  exit 1
fi

myloader_status=$(tail -n 10 ${myloader_log}|awk '{print $NF}'|grep '0'|wc -l)
if [ $myloader_status -ne 10 ];then
  echo  "$(date +"%Y%m%d %H:%M:%S") 数据导入失败 请检查错误信息" >>${log_redirection}
  exit 1
fi

}

import_data

# 配置主从信息
config_master_info() {
  echo "$(date "+%Y-%m-%d %H:%M:%S")  配置复制信息"  >>${log_redirection}

  mysql_binlog_file="$(sed  -n '3p'  $myloader_outputdir/metadata|awk '{print $3}')"
  mysql_binlog_pos="$(sed  -n '4p'  $myloader_outputdir/metadata|awk '{print $3}')"
  mysql_executed_gtid_set="$(sed  -n '5p'  $myloader_outputdir/metadata|awk '{print $3}')"


  if [ ${gtid_replic} == 'OFF' ];then
    $mysql_slave_conn -e "change master to master_host='${master_host}', master_port=${master_port}, master_user='${replic_user}', master_password='${replic_pwd}', master_log_file='${mysql_binlog_file}', master_log_pos=${mysql_binlog_pos}, master_delay=${replic_delay_time}, master_auto_position=0;" >>${log_redirection} 2>&1
    if [ $? -ne 0 ]; then
      echo "$(date +"%Y%m%d %H:%M:%S")  主从信息配置失败" >>${log_redirection}
      exit 1
    fi
  elif [ ${gtid_replic} == 'ON' ];then
    $mysql_slave_conn -e "reset master;set @@global.gtid_purged='${mysql_executed_gtid_set}';"
    $mysql_slave_conn -e "change master to master_host='${master_host}', master_port=${master_port}, master_user='${replic_user}', master_password='${replic_pwd}',  master_delay=${replic_delay_time}, master_auto_position=1;" >>${log_redirection} 2>&1
    if [ $? -ne 0 ]; then
      echo "$(date +"%Y%m%d %H:%M:%S")  主从信息配置失败" >>${log_redirection}
      exit 1
    fi
  fi

}
config_master_info

multi_thread_replic(){
  $mysql_slave_conn -e "set global slave_parallel_workers=${replic_threads_num}"
}
multi_thread_replic

start_replic_and_check(){
  $mysql_slave_conn -e "start slave" >>${log_redirection} 2>&1
  sleep 5
  replic_status=$($mysql_slave_conn -e "show slave status\G" 2>/dev/null|egrep  "Slave_IO_Running|Slave_SQL_Running"|grep -ic  'no')
  if [ ${replic_status} -eq 1 ]; then
    echo "$(date +"%Y%m%d %H:%M:%S")  主从状态异常 请检查" >>${log_redirection}
    exit 1
  fi

  echo "$(date +"%Y%m%d %H:%M:%S")  主从配置完成!!! 请在主库删除 ${master_user} 用户" >>${log_redirection}
  echo "$(date +"%Y%m%d %H:%M:%S")  主从配置完成!!! 请在主库删除 ${master_user} 用户" 
}
start_replic_and_check




cat $log_redirection


